package org.beetl.sql.core.db;

import lombok.Data;
import org.beetl.sql.annotation.builder.AttributeConvert;
import org.beetl.sql.annotation.entity.AssignID;
import org.beetl.sql.annotation.entity.AutoID;
import org.beetl.sql.annotation.entity.Seq;
import org.beetl.sql.annotation.entity.SeqID;
import org.beetl.sql.clazz.*;
import org.beetl.sql.clazz.kit.*;
import org.beetl.sql.core.*;
import org.beetl.sql.core.concat.*;
import org.beetl.sql.core.engine.template.SQLTemplateEngine;
import org.beetl.sql.core.meta.MetadataManager;
import org.beetl.sql.core.meta.SchemaMetadataManager;

import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *  标识了sql的共性和不同
 *
 * @author xiandafu
 */
public abstract class AbstractDBStyle implements DBStyle {

	//翻页从0还是1开始，默认从1开始
	public boolean offsetStartZero = false;
	protected NameConversion nameConversion;
	protected MetadataManager metadataManager;
	protected String lineSeparator = System.getProperty("line.separator", "\n");
	protected KeyWordHandler keyWordHandler = new DefaultKeyWordHandler();
	SQLTemplateEngine sqlTemplateEngine = null;

	protected  SQLManager sqlManager = null;

	public static AssignID DEFAULT_ASSIGNID = null;

	@Data
	public static class MockXXX{
		@AssignID String id;
	}
	static{
		DEFAULT_ASSIGNID = BeanKit.getAnnotation(MockXXX.class,"id",AssignID.class);
	}

	public AbstractDBStyle() {

	}

	@Override
	public void init(SQLTemplateEngine sqlTemplateEngine, Properties ps) {

		this.sqlTemplateEngine = sqlTemplateEngine;
		offsetStartZero = Boolean.parseBoolean(ps.getProperty("OFFSET_START_ZERO", "false").trim());
	}

	@Override
	public NameConversion getNameConversion() {
		return nameConversion;
	}

	@Override
	public void setNameConversion(NameConversion nameConversion) {
		this.nameConversion = nameConversion;
	}

	@Override
	public SQLSource genSelectById(Class<?> cls, Class viewType) {
		ConcatContext concatContext = this.createConcatContext();
		Select select = concatContext.select();
		appendIdCondition(cls, select);
		appendLogicFlagCondition(cls,select);
		select.from(cls);
		if (viewType != null) {
			select.all(cls, viewType);
		} else {
			select.all();
		}
		return new SQLTableSource(select.toSql(),SQLType.SELECT);
	}

	@Override
	public SQLSource genSelectByIds(Class<?> cls, Class viewType) {
		ConcatContext concatContext = this.createConcatContext();
		Select select = concatContext.select();
		appendJoinInIdsCondition(cls, select);
		appendLogicFlagCondition(cls,select);
		select.from(cls);
		if (viewType != null) {
			select.all(cls, viewType);
		} else {
			select.all();
		}
		return new SQLTableSource(select.toSql(),SQLType.SELECT);
	}


	@Override
	public SQLSource genExistSql(Class<?> cls) {
		ConcatContext concatContext = this.createConcatContext();
		Select select = concatContext.select();
		select.count().from(cls);
		appendIdCondition(cls, select);
		appendLogicFlagCondition(cls,select);
		return new SQLTableSource(select.toSql(),SQLType.SELECT);

	}

	@Override
	public SQLSource genSelectByIdForUpdate(Class<?> cls, Class viewType) {
		SQLSource source = genSelectById(cls, viewType);
		String template = source.getTemplate();
		template = template + " for update";
		source.setTemplate(template);
		return source;
	}

	@Override
	public SQLSource genSelectByTemplate(Class<?> cls, Class viewType) {
		ConcatContext concatContext = this.createConcatContext();
		Select select = concatContext.select();
		if (viewType == null) {
			select.all().from(cls);
		} else {
			select.all(cls, viewType).from(cls);
		}
		appendLogicFlagCondition(cls,select);
		getSelectTemplate(cls, select);
		return new SQLTableSource(select.toSql(),SQLType.SELECT);
	}

	@Override
	public SQLSource genSelectCountByTemplate(Class<?> cls) {
		ConcatContext concatContext = this.createConcatContext();
		Select select = concatContext.select();
		appendLogicFlagCondition(cls,select);
		select.count().from(cls);
		getSelectTemplate(cls, select);
		return new SQLTableSource(select.toSql(),SQLType.SELECT);
	}


	@Override
	public SQLSource genDeleteById(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, this.nameConversion);
		ConcatContext concatContext = this.createConcatContext();

		if (classDesc.getClassAnnotation().getLogicDeleteAttrName() == null) {
			Delete delete = concatContext.delete().from(cls);
			appendIdCondition(cls, delete);
			return new SQLTableSource(delete.toSql(),SQLType.DELETE);
		} else {
			Update update = concatContext.update().from(cls);
			appendIdCondition(cls, update);
			String col = this.nameConversion.getColName(cls, classDesc.getClassAnnotation().getLogicDeleteAttrName());
			Object value = classDesc.getClassAnnotation().getLogicDeleteAttrValue();
			update.assignConstants(col, value);
			return new SQLTableSource(update.toSql(),SQLType.DELETE);
		}


	}

	@Override
	public SQLSource genSelectAll(Class<?> cls, Class viewType) {
		Select select = this.createConcatContext().select().from(cls);
		select.from(cls);
		if (viewType == null) {
			select.all();
		} else {
			select.all(cls, viewType);
		}

		appendLogicFlagCondition(cls,select);
		String sql = select.toSql();
		return new SQLTableSource(sql,SQLType.SELECT);
	}

	@Override
	public SQLSource genUpdateById(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);

		Update update = getUpdate(cls);
		appendIdCondition(cls, update);
		appendLogicFlagCondition(cls,update);
		appendVersion(classDesc, update);

		return new SQLTableSource(update.toSql(),SQLType.UPDATE);
	}


	@Override
	public SQLSource genUpdateRawById(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		Update update = getRawUpdate(cls);
		appendIdCondition(cls, update);
		appendLogicFlagCondition(cls,update);
		return new SQLTableSource(update.toSql(),SQLType.UPDATE);
	}

	@Override
	public SQLSource genUpdateAbsolute(Class<?> cls) {
		//无条件更新所有，需要谨慎使用，子类可以抛出异常禁止这类方法调用
		Update update = getUpdate(cls);
		return new SQLTableSource(update.toSql(),SQLType.UPDATE);
	}


	@Override
	public SQLSource genUpdateTemplate(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);

		Update update = this.createConcatContext().update().from(cls);
		appendIdCondition(cls, update);
		appendLogicFlagCondition(cls,update);
		appendVersion(classDesc, update);


		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> properties = classDesc.getAttrs().iterator();

		List<String> idCols = classDesc.getIdCols();

		Map<String, AttributeConvert> attributeConvertMap = classDesc.getClassAnnotation().getExtAnnotation().getAttributeConvertMap();

		while (cols.hasNext() && properties.hasNext()) {
			String col = cols.next();
			String prop = properties.next();
			if (classDesc.getClassAnnotation().isUpdateIgnore(prop)) {
				continue;
			}
			if (idCols.contains(col)) {
				continue;
			}
			if (prop.equals(classDesc.getClassAnnotation().getVersionProperty())) {
				//版本字段
				update.assignVersion(col);
				continue;
			}
			if(attributeConvertMap.containsKey(prop)){
				AttributeConvert attributeConvert = attributeConvertMap.get(prop);
				String express = attributeConvert.toAutoSqlPart(this,cls,AutoSQLEnum.UPDATE_TEMPLATE_BY_ID,prop);
				if(express!=null){
					update.notEmptyAssign(express, col,prop);
					continue;
				}
			}
			update.notEmptyAssign(prop, col);

		}
		return new SQLTableSource(update.toSql(), SQLType.UPDATE);
	}

	@Override
	public SQLSource genUpdateAll(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);

		Update update = this.createConcatContext().update().from(cls);

		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> properties = classDesc.getAttrs().iterator();

		List<String> idCols = classDesc.getIdCols();
		while (cols.hasNext() && properties.hasNext()) {
			String col = cols.next();
			String prop = properties.next();
			if (classDesc.getClassAnnotation().isUpdateIgnore(prop)) {
				continue;
			}
			if (idCols.contains(col)) {
				continue;
			}
			if (prop.equals(classDesc.getClassAnnotation().getVersionProperty())) {
				//版本字段
				update.assignVersion(col);
				continue;
			}
			update.notEmptyAssign(prop, col);

		}
		appendLogicFlagCondition(cls,update);
		return new SQLTableSource(update.toSql(),SQLType.UPDATE);
	}

	@Override
	public SQLSource genInsert(Class<?> cls) {
		return generalInsert(cls, false);
	}


	@Override
	public SQLSource genInsertTemplate(Class<?> cls) {
		return generalInsert(cls, true);
	}


	protected SQLSource generalInsert(Class<?> cls, boolean template) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);

		Insert insert = this.createConcatContext().insert().into(cls);


		int idType = DBType.ID_ASSIGN;
		SQLTableSource source = new SQLTableSource();
		source.setSqlType(SQLType.INSERT);
		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> attrs = classDesc.getAttrs().iterator();

		List<String> idCols = classDesc.getIdCols();

		ClassAnnotation classAnnotation = classDesc.getClassAnnotation();


		Map<String, AttributeConvert> attributeConvertMap = classDesc.getClassAnnotation().getExtAnnotation().getAttributeConvertMap();

		while (cols.hasNext() && attrs.hasNext()) {
			String col = cols.next();
			String attr = attrs.next();
			if (classAnnotation.isInsertIgnore(attr)) {
				continue;
			}

			if(classAnnotation.isAutoAttr(attr)){
				insert.conditionalSet(col, attr);
				continue;
			}

			if(classAnnotation.isSeqAttr(attr)){
				Seq seq = BeanKit.getAnnotation(classDesc.getTargetClass(), attr
						, Seq.class);
				String seqExpress =  this.getSeqValue(seq.name());
				insert.conditionalSet(col,attr,seqExpress);
				continue;
			}

			if (attr.equals(classDesc.getClassAnnotation().getVersionProperty())
					&& classDesc.getClassAnnotation().getInitVersionValue() != -1) {
				//版本字段
				insert.setConstant(col, classDesc.getClassAnnotation().getInitVersionValue() + "");
				continue;
			}

			if (idCols.contains(col)) {
				idType = this.getIdType(classDesc.getTargetClass(), attr);
				if (idType == DBType.ID_AUTO) {
					insert.conditionalSet(col, attr);
					continue;
				} else if (idType == DBType.ID_SEQ) {
					SeqID seqId = BeanKit.getAnnotation(classDesc.getTargetClass(), attr,
							(Method) classDesc.getIdMethods().get(attr), SeqID.class);
					String seqExpress =  this.getSeqValue(seqId.name());
					insert.conditionalSet(col,attr,seqExpress);
					continue;
				} else if (idType == DBType.ID_ASSIGN) {
					//同其他字段一样，
				}
			}




			if (template) {
				if(attributeConvertMap.containsKey(attr)){
					AttributeConvert attributeConvert = attributeConvertMap.get(attr);
					String real = attributeConvert.toAutoSqlPart(this, cls,AutoSQLEnum.INSERT_TEMPLATE,attr);
					if(real!=null){
						insert.conditionalSetWIthReal(col, attr,real);
						continue;
					}

				}
				insert.conditionalSet(col, attr);
			} else {
				if(attributeConvertMap.containsKey(attr)){
					AttributeConvert attributeConvert = attributeConvertMap.get(attr);
					String expression = attributeConvert.toAutoSqlPart(this, cls,AutoSQLEnum.INSERT,attr);
					if(expression!=null){
						insert.setWithReal(col, expression,attr);
						continue;
					}

				}
				insert.set(col, attr);
			}

		}


		source.setTemplate(insert.toSql());
		source.setIdType(idType);
		source.setTableDesc(table);
		if (idType == DBType.ID_ASSIGN) {
			Map<String, AssignID> map = new HashMap<String, AssignID>();
			for (String idAttr : classDesc.getIdAttrs()) {
				AssignID assignId = BeanKit.getAnnotation(classDesc.getTargetClass(), idAttr, AssignID.class);
				if (assignId != null) {
					map.put(idAttr, assignId);
				}else{
					map.put(idAttr,	DEFAULT_ASSIGNID);
				}
			}
			source.setAssignIds(map);


		}

		return source;
	}


	protected void getSelectTemplate(Class<?> cls, WhereNode whereNode) {

		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);

		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> attrs = classDesc.getAttrs().iterator();

		while (cols.hasNext() && attrs.hasNext()) {
			String col = cols.next();
			String attr = attrs.next();
			whereNode.andIfNotEmpty(col, attr);

		}
	}

	protected Update getUpdate(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);
		Update update = this.createConcatContext().update().from(cls);
		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> properties = classDesc.getAttrs().iterator();
		List<String> idCols = classDesc.getIdCols();
		Map<String, AttributeConvert> attributeConvertMap = classDesc.getClassAnnotation().getExtAnnotation().getAttributeConvertMap();
		while (cols.hasNext() && properties.hasNext()) {
			String col = cols.next();
			String prop = properties.next();

			if (classDesc.getClassAnnotation().isUpdateIgnore(prop)) {
				continue;
			}
			if (idCols.contains(col)) {
				//主键不更新
				continue;
			}
			if (prop.equals(classDesc.getClassAnnotation().getVersionProperty())) {
				//版本字段
				update.assignVersion(col);
				continue;
			}
			if(attributeConvertMap.containsKey(prop)){
				AttributeConvert attributeConvert = attributeConvertMap.get(prop);
				String expressProp = attributeConvert.toAutoSqlPart(this, cls,AutoSQLEnum.UPDATE_BY_ID,prop);
				if(expressProp!=null){
					update.assign(col).real(prop).tplValue(expressProp);
					continue;
				}

			}
			update.assign(col).tplValue(prop);

		}
		return update;
	}

	protected Update getRawUpdate(Class<?> cls) {
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = this.metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);
		Update update = this.createConcatContext().update().from(cls);
		Iterator<String> cols = classDesc.getInCols().iterator();
		Iterator<String> properties = classDesc.getAttrs().iterator();
		List<String> idCols = classDesc.getIdCols();
		while (cols.hasNext() && properties.hasNext()) {
			String col = cols.next();
			String prop = properties.next();
			if (idCols.contains(col)) {
				//主键不更新
				continue;
			}
			update.assign(col).tplValue(prop);

		}
		return update;
	}

	protected void appendVersion(ClassDesc desc, WhereNode node) {
		String property = desc.getClassAnnotation().getVersionProperty();
		if (property == null) {
			return;
		}
		String col = this.nameConversion.getColName(desc.getTargetClass(), property);
		node.andEq(col, property);
	}


	/***
	 * 生成主键条件子句（示例 whrer 1=1 and id=${id}）
	 *
	 * @param cls
	 * @return
	 */
	protected void appendIdCondition(Class<?> cls, WhereNode node) {

		String tableName = nameConversion.getTableName(cls);
		TableDesc table = metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);
		List<String> colIds = classDesc.getIdCols();
		List<String> propertyIds = classDesc.getIdAttrs();
		this.checkId(colIds, propertyIds, cls.getName());
		Iterator<String> colIt = colIds.iterator();
		Iterator<String> propertyIt = propertyIds.iterator();

		if (colIt.hasNext() && propertyIt.hasNext()) {
			String colId = colIt.next();
			String properId = propertyIt.next();
			node.andEq(colId, properId);
			while (colIt.hasNext() && propertyIt.hasNext()) {
				colId = colIt.next();
				properId = propertyIt.next();
				node.andEq(colId, properId);
			}

		}

	}

	protected void appendLogicFlagCondition(Class<?> cls, WhereNode node) {
		if(!sqlManager.isQueryLogicDeleteEnable()){
			return ;
		}
		String tableName = nameConversion.getTableName(cls);
		TableDesc table = metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);
		if (classDesc.getClassAnnotation().getLogicDeleteAttrName() == null) {
			return ;
		}

		String col = this.nameConversion.getColName(cls, classDesc.getClassAnnotation().getLogicDeleteAttrName());
		Object value = classDesc.getClassAnnotation().getLogicDeleteAttrValue();

		node.andConstNotEq(col,String.valueOf(value));

	}



	protected void appendJoinInIdsCondition(Class<?> cls, WhereNode node) {

		String tableName = nameConversion.getTableName(cls);
		TableDesc table = metadataManager.getTable(tableName);
		ClassDesc classDesc = table.genClassDesc(cls, nameConversion);
		List<String> colIds = classDesc.getIdCols();
		if (colIds.size() == 1) {
			//通常情况
			String colId = colIds.get(0);
			node.andIn(colId, "ids");
			return ;
		}else{
			//转化成循环 (ID1=#{id1} and ID2=#{id2}) or (ID1=#{id.id1} and ID2=#{id.id2})
			LoopExpress loopExpress = node.loop("ids","id");
			List<String>  attrIds =  classDesc.getIdAttrs();
			for(int i=0;i<colIds.size();i++){
				String col = colIds.get(i);
				String attr = attrIds.get(i);
				loopExpress.addTpl(col,attr);
			}
		}





	}


	protected void checkId(Collection colsId, Collection attrsId, String clsName) {
		if (colsId.size() == 0 || attrsId.size() == 0) {
			throw new BeetlSQLException(BeetlSQLException.ID_NOT_FOUND,
					"主键未发现," + clsName + ",检查数据库表定义或者NameConversion");
		}
	}

	public String getOrderBy() {
		return lineSeparator + appendExpress("text(has(_orderBy)?' order by '+_orderBy)") + " ";
	}

	public String appendExpress(String express) {
		return sqlTemplateEngine.appendVar(express);
	}

	/**
	 * 根据注解来决定主键采用哪种方式生成。在跨数据库应用中，可以为一个id指定多个注解方式，如mysql，postgres 用auto，oracle 用seq
	 */
	@Override
	public int getIdType(Class c, String idProperty) {
		List<Annotation> ans = BeanKit.getAllAnnotation(c, idProperty);
		//默认是自增长
		int idType = DBType.ID_AUTO;

		for (Annotation an : ans) {
			if (an instanceof AutoID) {
				idType = DBType.ID_AUTO;
				break;
			} else if (an instanceof SeqID) {
				idType = DBType.ID_SEQ;
				break;
			} else if (an instanceof AssignID) {
				idType = DBType.ID_ASSIGN;
				break;
			}
		}

		return idType;

	}

	@Override
	public KeyWordHandler getKeyWordHandler() {
		return this.keyWordHandler;
	}

	@Override
	public void setKeyWordHandler(KeyWordHandler keyWordHandler) {
		this.keyWordHandler = keyWordHandler;
	}

	@Override
	public String getSeqValue(String seqName) {
		//子类覆盖
		throw new UnsupportedOperationException("不支持序列");
	}

	@Override
	public SQLExecutor buildExecutor(ExecuteContext executeContext) {
		SQLExecutor sqlExecutor = null;
		if (preparedStatementSupport()) {
			sqlExecutor = new BaseSQLExecutor(executeContext);
		} else {
			sqlExecutor = new BaseStatementOnlySQLExecutor(executeContext);
		}

		return sqlExecutor;
	}

	@Override
	public MetadataManager initMetadataManager(ConnectionSource cs) {
		metadataManager = new SchemaMetadataManager(cs, this);
		return metadataManager;
	}

	@Override
	public MetadataManager initMetadataManager(ConnectionSource cs, String defaultSchema, String defalutCatalog) {
		metadataManager = new SchemaMetadataManager(cs, defaultSchema, defalutCatalog, this);
		return metadataManager;
	}

	@Override
	public String wrapStatementValue(Object value) {
		if (preparedStatementSupport()) {
			throw new IllegalStateException("支持jdbc PreparedStatement，优先使用PreparedStatement提高性能，保证安全");
		}

		if (value == null) {
			return "null";
		}

		if (value instanceof String) {
			return "'" + value + "'";
		} else if (value instanceof Number) {
			return value.toString();
		} else if (value instanceof java.sql.Timestamp) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			String str = sdf.format((Date) value);
			return "parse_datetime('" + str + "',yyyy-MM-dd hh:mm:ss)";
		} else if (value instanceof java.sql.Date) {
			//TODO,确认
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String str = sdf.format((Date) value);
			return "parse_date('" + str + "',yyyy-MM-dd)";
		} else if (value instanceof java.util.Date) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			String str = sdf.format((Date) value);
			return "parse_datetime('" + str + "',yyyy-MM-dd hh:mm:ss)";
		} else if (value instanceof Boolean) {

			return ((Boolean) value).toString();
		} else {
			throw new IllegalArgumentException("不支持类型 " + value.getClass() + "," + value);
		}

	}

	protected ConcatContext createConcatContext() {
		ConcatContext concatContext = ConcatContext
				.createTemplateContext(this.nameConversion,
						this.keyWordHandler != null?this.getKeyWordHandler():new DefaultKeyWordHandler(), sqlTemplateEngine);
		return concatContext;
	}

	public boolean isOffsetStartZero() {
		return offsetStartZero;
	}

	@Override
	public void setOffsetStartZero(boolean offsetStartZero) {
		this.offsetStartZero = offsetStartZero;
	}

	@Override
	public String getDefaultSchema() {
		return null;
	}

	@Override
	public SQLTemplateEngine getSQLTemplateEngine() {
		return this.sqlTemplateEngine;
	}

	@Override
	public void config(SQLManager sqlManager) {
			this.sqlManager = sqlManager;
	}

}
